package eppic.pkg;

import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;
import android.content.Context;
import android.widget.Toast;
import android.database.Cursor;





public class DatabaseHelper extends SQLiteOpenHelper {
    
	static final String dbName="timeTrackerDB";
	
	public DatabaseHelper(Context context) {
		  super(context, dbName, null,5); 
		  }
    
	public void onCreate(SQLiteDatabase db) {
		 
		 //Clients
		db.execSQL("CREATE TABLE clients (_id INTEGER PRIMARY KEY , company_name TEXT, client_name TEXT, rate REAL, email TEXT, phone TEXT, notes BLOB)");
		 
		 //Projects
		 db.execSQL("CREATE TABLE projects (_id INTEGER PRIMARY KEY, name TEXT, client_id INTEGER, rate REAL)");
		 
		//Logs
		 db.execSQL("CREATE TABLE logs (_id INTEGER PRIMARY KEY, parent_id INTEGER, is_project INTEGER, start BLOB, end BLOB)");
		 
		//Expenses
		 db.execSQL("CREATE TABLE expenses (_id INTEGER PRIMARY KEY, parent_id INTEGER, is_project INTEGER, name TEXT, cost REAL)");
		 
		 //Settings
		 db.execSQL("CREATE TABLE settings (_id INTEGER PRIMARY KEY, default_rate REAL, round_time INTEGER)");
		
	}
	
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		  
		db.execSQL("DROP TABLE IF EXISTS clients");
		db.execSQL("CREATE TABLE clients (_id INTEGER PRIMARY KEY , company_name TEXT, client_name TEXT, rate REAL, email TEXT, phone TEXT, notes BLOB)");
		
		db.execSQL("DROP TABLE IF EXISTS logs");
		db.execSQL("CREATE TABLE logs (_id INTEGER PRIMARY KEY, parent_id INTEGER, is_project INTEGER, start BLOB, end BLOB)");
		
		db.execSQL("DROP TABLE IF EXISTS expenses");
		db.execSQL("CREATE TABLE expenses (_id INTEGER PRIMARY KEY, parent_id INTEGER, is_project INTEGER, name TEXT, cost REAL)");
	}
	
	public Client[] getAllClients()
	{
		
       
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c=db.rawQuery("SELECT * FROM clients", null);
       
        int count = c.getCount();
        Client[] all = new Client[count];
        
        c.moveToFirst();
        if(count > 0)
        {
        	for(int i = 0; i < count; i++)
        	{
	        	int id = c.getInt(c.getColumnIndex("_id"));
	        	String company = c.getString(c.getColumnIndex("company_name"));
	        	String client = c.getString(c.getColumnIndex("client_name"));
	        	float rate = c.getFloat(c.getColumnIndex("rate"));
	        	String email = c.getString(c.getColumnIndex("email"));
	        	String phone = c.getString(c.getColumnIndex("phone"));
	        	String notes = c.getString(c.getColumnIndex("notes"));
	        	
	        	
	        	Client temp_client = new Client(id, company, client, rate, email, phone, notes);
	            all[i] = temp_client;
	            c.moveToNext();
        	}
        }
        
        c.close();
        db.close();

		return all;
		
	}
	
	public Project[] getAllProjects()
	{
			
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c=db.rawQuery("SELECT * FROM projects", null);

       
        int count = c.getCount();
        Project[] all = new Project[count];
        
        c.moveToFirst();
        if(count > 0)
        {
        	for(int i = 0; i < count; i++)
        	{
	        	int id = c.getInt(c.getColumnIndex("_id"));
	        	String name = c.getString(c.getColumnIndex("name"));
	        	int client_id = c.getInt(c.getColumnIndex("client_id"));
	        	float rate = c.getFloat(c.getColumnIndex("rate"));

	        	
	        	
	        	Project temp_project = new Project(id, name, client_id, rate);
	            all[i] = temp_project;
	            c.moveToNext();
        	}
        }
        
        c.close();
        db.close();

		return all;
		
	}
	
	public Project[] getAllProjects(int parent)
	{
			
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c=db.rawQuery("SELECT * FROM projects WHERE client_id="+parent, null);

       
        int count = c.getCount();
        Project[] all = new Project[count];
        
        c.moveToFirst();
        if(count > 0)
        {
        	for(int i = 0; i < count; i++)
        	{
	        	int id = c.getInt(c.getColumnIndex("_id"));
	        	String name = c.getString(c.getColumnIndex("name"));
	        	int client_id = c.getInt(c.getColumnIndex("client_id"));
	        	float rate = c.getFloat(c.getColumnIndex("rate"));

	        	
	        	
	        	Project temp_project = new Project(id, name, client_id, rate);
	            all[i] = temp_project;
	            c.moveToNext();
        	}
        }
        
        c.close();
        db.close();

		return all;
		
	}
	
	//Methods for Statistics
	
	
	
}